-- =============================================
-- Gets publishing requests that need to be executed.
--
-- Modifications
-- 06/05/2003  E.Tatsui   Converted from CFML.
-- =============================================

CREATE                                 PROCEDURE [dbo].[amsp_CMGetPublishRequests]
  @InServerCode char(1),
  @InServerIPAddress varchar(15),
  @InPublishRegenerateInd char(1)
AS

BEGIN

  DECLARE
    @PublishRequestDetailID numeric,
    @GECodePath varchar(255),
    @DeleteFlag char(1),
    @PublishingSpeed int,
    @RequestNum int,
    @SQL nvarchar (2500)

  SET @PublishingSpeed = 100

  SELECT @PublishingSpeed = PublishingSpeed
    FROM Publish_Server_Ref
   WHERE PublishServerCode = @InServerCode

  CREATE TABLE #PublishRequests
  (NextPublishDate datetime,
   PublishRequestID integer,
   PublishRequestDetailID integer,
   ContentID integer,
   SourceRootLocation varchar(255) COLLATE database_default,
   PublishRootPath varchar(255) COLLATE database_default,
   ProtectedRootPath varchar(255) COLLATE database_default,
   PublishRegenerateInd char(1) COLLATE database_default,
   WebsiteKey uniqueidentifier,
   ReloadMenuURL varchar(255) COLLATE database_default,
   DefaultServerFlag char(1) COLLATE database_default,
   DeleteFlag char(1) COLLATE database_default,
   PublishPriorityCode smallint)

  -- SET ROWCOUNT @PublishingSpeed
  SET @SQL = 'INSERT INTO #PublishRequests
         SELECT TOP ' + CAST(@PublishingSpeed as varchar(20)) +
         ' DATEADD(second, prd.Frequency, prd.PublishedDateTime) AS NextPublishDate,
         pr.PublishRequestID,
         prd.PublishRequestDetailID,
         Convert(int,prd.ContentID),
         psr.SourceRootLocation,
         w.PublishRootPath,
         w.ProtectedRootPath,
         prd.PublishRegenerateInd,
         w.WebsiteKey,
         w.WebsiteRootURL,
         ''Y'',
         prd.DeleteFlag,
         pr.PublishPriorityCode
    FROM Publish_Request pr WITH (NOLOCK), Publish_Request_Detail prd WITH (NOLOCK),
         Website w WITH (NOLOCK), Publish_Server_Ref psr WITH (NOLOCK), Content c
   WHERE pr.PublishRequestID = prd.PublishRequestID
     AND prd.WebsiteKey = w.WebsiteKey
     AND prd.PublishServerCode = psr.PublishServerCode
     AND prd.PublishRequestID = pr.PublishRequestID
     AND prd.ContentID = c.ContentID
     AND (c.PublishDateTime IS NULL OR c.PublishDateTime < GetDate())
     AND (prd.PublishRequestStatusCode = ''N''
          OR ((DATEADD(second, prd.Frequency, prd.PublishedDateTime) <= GetDate())
               AND (prd.Frequency <> 0))
          )
     AND pr.RequestDateTime <= GetDate()
     AND psr.ServerIPAddress = ''' + @InServerIPAddress + '''' +
    'AND psr.PublishServerCode = ''' + @InServerCode + '''' +
    'AND IsNull(prd.PublishRegenerateInd,''P'') = ''' + @InPublishRegenerateInd + '''' +
  'ORDER BY pr.PublishPriorityCode, NextPublishDate, prd.PublishRequestDetailID'

  --print @SQL

  EXEC sp_executesql @SQL

  SET @RequestNum = @@Rowcount

  --SET ROWCOUNT 0

  ALTER TABLE #PublishRequests
  ALTER COLUMN PublishRequestDetailID int NULL

  ALTER TABLE #PublishRequests
  ALTER COLUMN ReloadMenuURL varchar(500) COLLATE database_default

  -- If this is publish requests, add requests to publish navigaiton.
  IF @InPublishRegenerateInd = 'P' AND @RequestNum < @PublishingSpeed
     INSERT INTO #PublishRequests
     SELECT  NULL AS NextPublishDate,
             pr.PublishRequestID,
             NULL,
             NULL AS ContentID,
             psr.SourceRootLocation,
             w.PublishRootPath,
             w.ProtectedRootPath,
             'P',
             w.WebsiteKey,
             w.WebsiteRootURL,
             'Y' AS DefaultServerFlag,
             'N' AS DeleteFlag,
             pr.PublishPriorityCode
        FROM Publish_Request pr WITH (NOLOCK) LEFT OUTER JOIN Publish_Request_Detail prd WITH (NOLOCK)
          ON pr.PublishRequestID = prd.PublishRequestID
         AND IsNull(prd.PublishRegenerateInd,'P') = 'P'
        LEFT OUTER JOIN Publish_Message_Log pml WITH (NOLOCK)
          ON pr.PublishRequestID = pml.PublishRequestID, Nav_Menu nm WITH (NOLOCK),
             Website w WITH (NOLOCK), Publish_Server_Ref psr WITH (NOLOCK)
       WHERE pr.NavMenuID = nm.NavMenuID
         AND nm.WebsiteKey = w.WebsiteKey
         AND w.PublishServerCode = psr.PublishServerCode
         AND (pr.PublishedDateTime is NULL)
         AND pr.RequestDateTime <= GetDate()
         AND psr.ServerIPAddress = @InServerIPAddress
         AND psr.PublishServerCode = @InServerCode
         AND prd.PublishRequestID IS NULL
         AND pml.PublishRequestID IS NULL

  SELECT @GECodePath = Value
    FROM System_Variable WITH (NOLOCK)
   WHERE Name = 'GECodePath'

  IF Len(@GECodePath) > 1
    SET @GECodePath = RIGHT(@GECodePath,Len(@GECodePath)-1)
  ELSE IF @GECodePath = '/'
    SET @GECodePath = ''

  UPDATE #PublishRequests
     SET ReloadMenuURL = ReloadMenuURL + @GECodePath + 'NavMenuSetup.cfm'

  CREATE TABLE #PublishContent
               (PublishRequestDetailID numeric,
                SourceLocation varchar(255) COLLATE database_default,
                PublishLocation varchar(255) COLLATE database_default,
                PublishDirectory varchar(255) COLLATE database_default,
                Header varchar(500) COLLATE database_default,
                Footer varchar(500) COLLATE database_default,
                ContentFileID int,
                HTMLContentID int,
                DefaultContentFlag char(1) DEFAULT('N') COLLATE database_default,
                RemoveFlag char(1) DEFAULT('N') COLLATE database_default,
                UseProtectedPath char(1) DEFAULT ('N') COLLATE database_default,
                RedirectFileFlag char(1) DEFAULT('N') COLLATE database_default,
                DeleteFolderFlag char(1) DEFAULT ('N') COLLATE database_default,
                DeleteFolderContentFlag char(1) DEFAULT('N') COLLATE database_default)

  DECLARE c_Request CURSOR FOR
   SELECT PublishRequestDetailID,
          DeleteFlag
     FROM #PublishRequests

  OPEN c_Request
  FETCH NEXT FROM c_Request
   INTO @PublishRequestDetailID,
        @DeleteFlag
  WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC amsp_CMGetContentToPublish @PublishRequestDetailID, @DeleteFlag

    FETCH NEXT FROM c_Request
     INTO @PublishRequestDetailID,
          @DeleteFlag
  END
  CLOSE c_Request
  DEALLOCATE c_Request

  SELECT DISTINCT
         a.WebsiteKey,
         a.NextPublishDate,
         a.PublishRequestID,
         a.PublishRequestDetailID,
         a.ContentID,
         a.SourceRootLocation,
         CASE WHEN b.UseProtectedPath = 'Y' THEN a.ProtectedRootPath
              ELSE a.PublishRootPath END AS PublishRootPath,
         a.PublishRegenerateInd,
         a.WebsiteKey,
         a.ReloadMenuURL,
         a.DefaultServerFlag,
         a.PublishPriorityCode,
         b.SourceLocation,
         b.PublishLocation,
         b.PublishDirectory,
         b.Header,
         b.Footer,
         b.ContentFileID,
         b.HTMLContentID,
         b.DefaultContentFlag,
         b.RemoveFlag,
         b.RedirectFileFlag,
         b.DeleteFolderFlag,
         b.DeleteFolderContentFlag,
         b.PublishRequestDetailID AS ContentExists
    FROM #PublishRequests a LEFT OUTER JOIN #PublishContent b
      ON a.PublishRequestDetailID = b.PublishRequestDetailID
  ORDER BY a.PublishPriorityCode, NextPublishDate, a.PublishRequestID, a.PublishRequestDetailID, b.RemoveFlag DESC, b.DefaultContentFlag
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetPublishRequests] TO [IMIS]
GO